﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


/// <summary>
/// Summary description for DALKAT
/// </summary>
public class DALKAT
{
    public DALKAT()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    static string connStr = ConfigurationManager.ConnectionStrings["SkillifyConnectionString"].ConnectionString;
    static SqlConnection con = new SqlConnection(connStr);
    //static SqlConnection con = new SqlConnection(@"data source=182.50.141.238;initial catalog=SkilledRESKAT;user id=tyros;password=Gits123%");
    //static SqlConnection con = new SqlConnection(@"data source=.;Initial catalog=SkilledRESKAT;Integrated Security=true");
    static SqlCommand cmd;
    static SqlDataAdapter da;
    static DataTable dt;


    public static DataTable GetSubjects()
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSubjects";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TransType", 1);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetSubjectInfo()
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_ManngeSubject";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TransType", 2);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable GetSubjectInfoById(int id)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_GetSubjects";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@subId", id);
        cmd.Parameters.AddWithValue("@TransType", 2);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataTable getTopicRelatedToSubject(string subName)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_getTopicRelatedToSubject";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@subName", subName);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static void ManngeSubject(string subName, string topicName)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_ManngeSubject";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@subName", subName);
        cmd.Parameters.AddWithValue("@topicName", topicName);
        cmd.Parameters.AddWithValue("@TransType", 1);
        if (con.State == ConnectionState.Open)
            con.Close();
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

    public static void ManngeSubjectUpdation(string subName, string topicName)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "SP_ManngeSubject";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@subName", subName);
        cmd.Parameters.AddWithValue("@topicName", topicName);
        cmd.Parameters.AddWithValue("@TransType", 3);
        if (con.State == ConnectionState.Open)
            con.Close();
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

    public static DataTable GetTestListScheduled(int userId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetTestList";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@UserID", userId);
        cmd.Parameters.AddWithValue("@Status", "Scheduled");
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }
    public static DataTable GetTestInfoByTestId(int TestId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetTestInfoByTestId";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TestID", TestId);
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

    public static DataSet GetTopicListByTopicId(string TopicId)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetTopicListsById";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TopicID", TopicId);
        da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }

    public static DataSet GetQuestions(int TestId)
    {
        cmd = new SqlCommand();
        DataSet dsQustn = new DataSet();
        cmd.Connection = con;
        cmd.CommandText = "sp_TestInProgress";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TestId", TestId);
        cmd.Parameters.AddWithValue("@TransType", 1);
        SqlDataAdapter dtqtn = new SqlDataAdapter(cmd);

        dtqtn.Fill(dsQustn);

        return dsQustn;

    }

    public static DataSet GetOptions(int QuestionID)
    {
        
        cmd = new SqlCommand();
        DataSet dsOptn = new DataSet();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetOptions";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@QuestionID",QuestionID);
        SqlDataAdapter dtoptn = new SqlDataAdapter(cmd);

        dtoptn.Fill(dsOptn, "Options");

        return dsOptn;

    }


    public static DataTable GetAnswer(int QuestionID)
    {

        cmd = new SqlCommand();
        DataTable dtAns = new DataTable();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetAnswers";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@QuestionID", QuestionID);
        cmd.Parameters.AddWithValue("@TransType", 1);
        SqlDataAdapter dtoptn = new SqlDataAdapter(cmd);

        dtoptn.Fill(dtAns);

        return dtAns;

    }

    public static DataTable GetRightOptionId(int QuestionID)
    {

        cmd = new SqlCommand();
        DataTable dtAns = new DataTable();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetAnswers";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@QuestionID", QuestionID);
        cmd.Parameters.AddWithValue("@TransType", 2);
        SqlDataAdapter dtoptn = new SqlDataAdapter(cmd);

        dtoptn.Fill(dtAns);

        return dtAns;

    }

    public static DataTable GetTestInProgressFields(int TestID)
    {
        cmd = new SqlCommand();
        DataTable dtFields = new DataTable();
        cmd.Connection = con;
        cmd.CommandText = "sp_GetTestInProgressFields";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@testid", TestID);
        SqlDataAdapter daFields = new SqlDataAdapter(cmd);
        daFields.Fill(dtFields);
        return dtFields;
    }
}